# -*- coding:utf-8 -*-

from dbbasic import dbMgr
from Models import Sheet
def save(sheet):
	with dbMgr() as cursor:
		#cursor.execute('create table sheet(id integer primary key autoincrement, createuser varchar(200),title varchar(200), sheetType varchar(10), sheetid varchar(20),nexturl varchar(200),urlName varchar(200), sheetId2 varchar(200), sheetType2 varchar(200),processDefName varchar(200), baseSheetId varchar(200), activeDefId varchar(200),activeInsId varchar(200), workItemId varchar(200),workItemState varchar(200),processInsId varchar(200),processedFlag boolean,reason varchar(200),sheetTime varchar(30))')
		if(get(sheet.sheetid) == None):
			cursor.execute('insert into sheet values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' , (sheet.createuser,sheet.title,sheet.sheetType,sheet.sheetid,sheet.nexturl,sheet.urlName,sheet.sheetId2,sheet.sheetType2,sheet.processDefName,sheet.baseSheetId,sheet.activeDefId,sheet.activeInsId,sheet.workItemId,sheet.workItemState,sheet.processInsId,sheet.processedFlag,sheet.reason,sheet.sheetTime))
		
def get(sheetId):
	if isinstance(sheetId, int):
		with dbMgr() as cursor:
			cursor.execute('select * from sheet where id=?', (sheetId,))
			value = cursor.fetchone()
			if value == None:
				return None
			sheet = Sheet(*value[1:6])
			sheet.id = value[0]
			sheet.processedFlag = value[-3]
			sheet.reason = value[-2]
			sheet.sheetTime = value[-1]
			return sheet
	if isinstance(sheetId, unicode) or isinstance(sheetId, str):
		with dbMgr() as cursor:
			cursor.execute('select * from sheet where sheetid=?', (sheetId,))
			value = cursor.fetchone()
			if value == None:
				return None
			sheet = Sheet(*value[1:6])
			sheet.id = value[0]
			sheet.processedFlag = value[-3]
			sheet.reason = value[-2]
			sheet.sheetTime = value[-1]
			return sheet

def list(pageNo=1, pageSize=1000000,**condition):
	con = ''
	for c in condition:
		if isinstance(condition[c], int):
			con = con + '%s=%d and ' % (c, condition[c])
		if isinstance(condition[c], str):
			con = con + '%s=\'%s\' and ' % (c, condition[c])
	if not len(con) == 0:
		con = ' where ' + con
	con = con[0:len(con)-4]
	with dbMgr() as cursor:
		cursor.execute('select * from sheet' + con +' order by id desc limit ? offset ?',  (pageSize, (pageNo - 1) * pageSize))
		values = cursor.fetchall()
		sheets = []

		for v in values:
			sheets.append(get(v[0]))
		return sheets
	
def update(sheet):
	if sheet.id == None:
		print 'object has no id, must save first'
		return
	with dbMgr() as cursor:
		cursor.execute('update sheet set createuser=?,title=?,sheetType=?,sheetid=?,nexturl=?,urlName=?,sheetId2=?,sheetType2=?,processDefName=?,baseSheetId=?,activeDefId=?,activeInsId=?,workItemId=?,workItemState=?,processInsId=?,processedFlag=?,reason=?, sheetTime=? where id=?', (sheet.createuser,sheet.title,sheet.sheetType,sheet.sheetid,sheet.nexturl,sheet.urlName,sheet.sheetId2,sheet.sheetType2,sheet.processDefName,sheet.baseSheetId,sheet.activeDefId,sheet.activeInsId,sheet.workItemId,sheet.workItemState,sheet.processInsId,sheet.processedFlag, sheet.reason,sheet.sheetTime, sheet.id))
	
def delete(sheet):
	if isinstance(sheet, Sheet):
		if sheet.id == None:
			print 'object has no id, must save first'
			return

		with dbMgr() as cursor:
			cursor.execute('delete from sheet where id=?', (sheet.id,))
	if isinstance(sheet, int):
		with dbMgr() as cursor:
			cursor.execute('delete from sheet where id=?', (sheet,))
			
		
	